using System;
using System.Data;
using MySql.Data.MySqlClient;
using System.Collections.Generic;
using System.IO;

namespace QQZiFramework.DataBase.MySql
{
    class MySqlAccess : IDBAccess
    {
        public MySqlAccess(DBContext context)
            : base(context)
        {

        }
        public override void Create(string connectionString)
        {
            conStr = connectionString;
        }
        // public override string ConcatFunction()
        // {
        //     return "concat";
        // }

        public override string ConcatSql(params string[] args) 
        {
            return "concat("+ string.Join(',',args) +")";
        }

        public override IDbConnection GetConnection()
        {
            return conn;
        }
        public override void BeginTransaction()
        {
            if (trans != null)
            {
                throw new Exception("有未提交的事务，无法再创建！");
            }
            OpenSql();
            trans = conn.BeginTransaction();
            mContext.LogSqlMessage("DataBase BeginTransaction", true);
        }
        public override DataTable QueryTable(string sql)
        {
            return QueryTable(sql, CommandType.Text);
        }

        public override IDataReader QueryReader(string sql, CommandType cmdType, params System.Data.IDbDataParameter[] parameters)
        {
            if (reader != null && !reader.IsClosed) throw new ClientException("有已打开的Reader，请先调用reader.close方法关闭");
            var cmd = CreateCommand(sql, cmdType, parameters, true);
            reader = cmd.ExecuteReader();
            cmd.Parameters.Clear();
            cmd = null;
            return reader;
        }

        string lastSql;
        MySqlConnection conn;
        MySqlTransaction trans;
        //MySqlCommand cmd;
        MySqlDataReader reader;
        string conStr;

        private void OpenSql()
        {
            if (conn == null) conn = new MySqlConnection(conStr);
            if (conn.State == ConnectionState.Closed)
            {
                conn.Open();
            }
        }

        /// <summary>
        /// 是否存在表
        /// </summary>
        public override bool IsTableExists(string tableName)
        {
            string sql = "select 1 from information_schema.TABLES t where  t.TABLE_NAME ='" + tableName + "' and t.TABLE_SCHEMA ='" + GetDBName() + "'";
            object scalar = ExecScalar(sql);
            if (scalar != null && scalar != DBNull.Value) return true;
            else return false;
        }


        // private MySqlCommand CreateCommand(string sql, bool query = false)
        // {
        //     OpenSql();
        //     MySqlCommand cmd = new MySqlCommand(sql, conn);
        //     if (trans != null) cmd.Transaction = trans;
        //     cmd.CommandType = CommandType.Text;
        //     cmd.CommandText = sql;
        //     lastSql = sql;
        //     mContext.LogSqlMessage(sql, query);
        //     return cmd;
        // }
        private MySqlCommand CreateCommand(string sql, CommandType cmdType, System.Data.IDbDataParameter[] parameters, bool query)
        {
            OpenSql();
            MySqlCommand cmd = new MySqlCommand(sql, conn);
            if (conn.ConnectionTimeout > 60) cmd.CommandTimeout = conn.ConnectionTimeout;
            cmd.CommandText = sql;
            cmd.CommandType = cmdType;
            if (parameters != null && parameters.Length > 0)
            {
                cmd.Parameters.AddRange(parameters);
                sql += "(";
                foreach (MySqlParameter parameter in parameters)
                {
                    sql += parameter.ParameterName + "=";
                    if (parameter.Value != null && parameter.Value.ToString().Length > 255) sql += "长内容";
                    else sql += parameter.Value;
                    sql += ",";
                }
                sql = sql.Substring(0, sql.Length - 1) + ")";
            }
            if (trans != null) cmd.Transaction = trans;
            lastSql = sql;
            mContext.LogSqlMessage(sql, query);
            return cmd;
        }

        public override int ExecuteCommand(string sql)
        {
            return ExecuteCommand(sql, CommandType.Text);
        }



        public override object ExecScalar(string sql)
        {
            return ExecScalar(sql, CommandType.Text);
        }

        public override IDbDataParameter CreateParameter(string name, object value)
        {
            return new MySqlParameter("@" + name, value);
        }

        /// <summary>
        /// 是否有自增功能
        /// </summary>
        /// <value></value>
        public override bool AotoIdentity
        {
            get { return true; }
        }
        static Dictionary<string, string> functionFormat = new Dictionary<string, string>();

        static MySqlAccess()
        {
            functionFormat.Add("date", "date_format({0}, '%Y-%m-%d')");
            functionFormat.Add("datetime", "date_format({0}, '%Y-%m-%d %H:%i:%S')");
            functionFormat.Add("isnull", "IFNULL({0},{1})");
            functionFormat.Add("to_date", "str_to_date({0},'%Y-%m-%d')");
            functionFormat.Add("to_datetime", "str_to_date({0},'%Y-%m-%d %T')");
        }

        public override Dictionary<string, string> FunctionFormatMap
        {
            get { return functionFormat; }
        }
        
        public override string GetNewIdentitySql(string tableName)
        {
            return null;
        }

        public override string GetInsertedIdentitySql(string tableName)
        {
            return "select @@identity";
        }

        public override void Dispose()
        {
            if (conn != null)
            {
                if (conn.State == ConnectionState.Open)
                {
                    if (trans != null) trans.Rollback();
                    if (reader != null && !reader.IsClosed) reader.Close();
                    conn.Close();
                }
                //if (cmd != null) cmd.Dispose();
                conn.Dispose();
                conn = null;
                //cmd = null;
                trans = null;
            }
        }


        public override string GetLastSql()
        {
            return lastSql;
        }

        public override void Commit()
        {
            if (conn != null && conn.State == ConnectionState.Open)
            {
                if (trans != null)
                {
                    trans.Commit(); trans = null;
                    mContext.LogSqlMessage("DataBase Commit", true);
                }
            }
        }
        public override void Rollback()
        {
            if (conn != null && conn.State == ConnectionState.Open)
            {
                if (trans != null)
                {
                    trans.Rollback(); trans = null;
                    mContext.LogSqlMessage("DataBase Rollback", true);
                }
            }
        }
        public override void Close()
        {
            if (conn != null && conn.State == ConnectionState.Open)
            {
                if (reader != null && !reader.IsClosed) reader.Close();
                if (trans != null) { trans.Commit(); trans = null; }
                conn.Close();
            }
        }


        /// <summary>
        /// 分页查询
        /// </summary>
        /// <param name="select"></param>
        /// <param name="table"></param>
        /// <param name="where"></param>
        /// <param name="orderby"></param>
        /// <param name="start">起始位置，从1开始</param>
        /// <param name="end">结束位置</param>
        public override String PartQuerySql(string select, string table, string where, string orderby, int start, int end){
            if (orderby == null) throw new ArgumentNullException("pagger query order by");
            start--;
            end--;
            string sql = "select " + select + " from " + table;
            if (!string.IsNullOrEmpty(where)) sql += " where " + where;
            sql += " order by " + orderby;
            sql += " limit " + start + "," + (end - start + 1);
            return sql;
        }   

        // public override String PartQuerySql(string select, string table, string where, string orderby, IDbDataParameter pageIndex, IDbDataParameter pageSize){
        //      if (orderby == null) throw new ArgumentNullException("pagger query order by");
        //       string sql = "set @offset = "+pageSize.ParameterName+" * ("+pageIndex.ParameterName+" - 1);select " + select + " from " + table;
        //     if (!string.IsNullOrEmpty(where)) sql += " where " + where;
        //     sql += " order by " + orderby;
        //     sql += " limit @offset," + pageSize.ParameterName;
        //     return sql;
        // } 


        public override DataTable QueryTable(string text, CommandType cmdType, params System.Data.IDbDataParameter[] parameters)
        {
            var cmd = CreateCommand(text, cmdType, parameters, true);
            if (parameters != null)
            {
                cmd.Parameters.Clear();
                cmd.Parameters.AddRange(parameters);
            }
            System.Data.DataTable dt = new DataTable();
            MySqlDataAdapter da = new MySqlDataAdapter(cmd);
            da.Fill(dt);
            cmd.Parameters.Clear();
            cmd = null;
            return dt;
        }

        public override int ExecuteCommand(string text, CommandType cmdType, params IDbDataParameter[] parameters)
        {
            var cmd = CreateCommand(text, cmdType, parameters, false);
            cmd.CommandType = cmdType;
            int val = cmd.ExecuteNonQuery();
            cmd.Parameters.Clear();
            cmd = null;
            return val;
        }

        public override object ExecScalar(string text, CommandType cmdType, params System.Data.IDbDataParameter[] parameters)
        {
            var cmd = CreateCommand(text, cmdType, parameters, text.IndexOf("insert", StringComparison.OrdinalIgnoreCase) == -1);
            object obj = cmd.ExecuteScalar();
            cmd.Parameters.Clear();
            cmd = null;
            return obj;
        }

        public override string GetDateConverter(int length)
        {
            return "{0}";
        }

        public override void CreateTreeExtendTableIfNotExists(string treeTableName)
        {
            ExecuteCommand("if not exists ( select  id from sysobjects where name = '" + treeTableName + "_REX' and type = 'U' ) Create TABLE " + treeTableName + "_REX( NodeId int  not null,     Deep int not null,     SubNodeId int not Null)");
        }

        public override void BulkInsert(string tableName, DataTable dataTable)
        {
            // OpenSql();         

            // // Create a MySqlBulkLoader instance
            // MySqlBulkLoader bulkLoader = new MySqlBulkLoader(conn);
            // bulkLoader.TableName = tableName;
            // bulkLoader.FieldTerminator = ",";
            // bulkLoader.LineTerminator = "\n";

            // // Optionally, specify column mappings if DataTable columns do not exactly match table columns
            // // bulkLoader.ColumnMappings.Add("ColumnInDataTable", "ColumnInTable");

            // // Write DataTable to a temporary file
            // string tempCsvFile = Path.GetTempFileName();
            // StreamWriter streamWriter = new StreamWriter(tempCsvFile);
            // dataTable.TableName = tableName;
            // dataTable.WriteXml(streamWriter, XmlWriteMode.WriteSchema, false);
            // streamWriter.Close();
            // // Load data from the file into the table
            // bulkLoader.FileName = tempCsvFile;
            // int rowsAffected = bulkLoader.Load();
            // Console.WriteLine($"Inserted {rowsAffected} rows into {tableName}.");
            // // Clean up temporary file
            // File.Delete(tempCsvFile);
            string strInsert = "insert into " + tableName + "(";
            string columns = "";
            foreach (DataColumn dataColumn in dataTable.Columns)
            {
                columns += "," + dataColumn.ColumnName;
            }
            strInsert += columns.Substring(1);
            strInsert += ") values ";
            int rowIndex = 0;
            string strRow = "";
            foreach (DataRow dr in dataTable.Rows)
            {
                rowIndex++;  
                string currentRow = "";              
                foreach (DataColumn dataColumn in dr.Table.Columns)
                {
                    currentRow += "," +  formatValueForDB(dataColumn.DataType, dr[dataColumn]);
                }
                strRow += "(" + currentRow.Substring(1) + "),";
                if (rowIndex % 20 == 0 || rowIndex == dataTable.Rows.Count)
                {
                    string sql = strInsert + strRow.Substring(0, strRow.Length - 1);
                    ExecuteCommand(sql);
                    strRow= "";
                }
            }

        }

        private string formatValueForDB(Type t, object value)
        {
            if(value ==null) return "null";
            switch (t.Name)
            {
                case "Int32":
                case "Int64":
                case "Single":
                case "Double":
                case "Decimal":
                    return value.ToString();
                case "Boolean":
                    return value.Equals(true) ? "1" : "0";
                case "DateTime":
                    return "'" + ((DateTime)value).ToString("yyyy-MM-dd HH:mm:ss") + "'";
                default:
                    return "'" + value + "'";
            }
        }


        /// <summary>
        /// 获取属性的类型转为数据库中定义的类型
        /// </summary>
        public override string ConvertDataType(Type propertyType, bool withSize)
        {
            throw new NotImplementedException();
            // switch (propertyType.Name)
            // {
            //     case "Int32":
            //     case "Int64":
            //         return "int"; ;
            //     case "Single":
            //     case "Double":
            //         return "float";
            //     case "Decimal":
            //         if (withSize) return "decimal(18,2)";
            //         else return "decimal";
            //     case "DateTime":
            //         return "datetime";
            //     default:
            //         if (withSize) return "varchar(255)";
            //         else return "varchar";
            // }
        }
        /// <summary>
        /// 创建临时表（通常是根据DataTable)
        /// </summary>
        public override string CreateTempTable(string tableName, DataColumn[] allColumns, DataColumn createPrimaryKey)
        {
            if (allColumns.Length == 0) throw new ArgumentNullException("allColumns");
            string tmpName = "#" + tableName + DateTime.Now.ToString("dd");
            string sql = "CREATE TABLE " + tmpName + " (";
            string columnSql = "";
            foreach (var dc in allColumns)
            {
                columnSql += "," + dc.ColumnName + " " + ConvertDataType(dc.DataType, true);
                if (dc == createPrimaryKey) columnSql += " primary key";
            }
            sql += columnSql.Substring(1) + ")";
            ExecuteCommand(sql);
            return tmpName;
        }

        /// <summary>
        /// 根据临时表的内容，更新物理表
        /// <param name="primaryKey">主键名</param>
        /// <param name="updateColumns">要更新的列</param>
        /// </summary>
        public override void UpdateByTempTable(string tableName, string tempTableFullName, DataColumn[] updateColumns, DataColumn primaryKeyColumn)
        {
            var primaryKey = primaryKeyColumn.ColumnName;
            string sql = "update a set a." + updateColumns[0].ColumnName + "=b." + updateColumns[0].ColumnName;
            for (int i = 1; i < updateColumns.Length; i++)
            {
                sql += ",a." + updateColumns[i].ColumnName + "=b." + updateColumns[i].ColumnName;
            }
            sql += " from " + tableName + " a  join " + tempTableFullName + " b on a." + primaryKey + " = b." + primaryKey;
            ExecuteCommand(sql);
        }


        /// <summary>
        /// 删除临时表
        /// </summary>
        public override void DropTempTable(string tableName)
        {
            ExecuteCommand("drop table #" + tableName);
        }

        public override bool IsColumnExists(string tableName, string columnName)
        {
            //是否存在配置表
            object scalar = ExecScalar("SELECT  1 FROM  `information_schema`.`COLUMNS` where  TABLE_NAME='" + tableName + "' and COLUMN_NAME ='" + columnName + "'");
            if (scalar != null && scalar != DBNull.Value) return true;
            else return false;
        }

        private string GetDBName()
        {
            string dbName = Utility.getMiddle(conStr, "Catalog=", ";");
            if (dbName == null) throw new Exception("连接串中没有Catalog=xxx");
            return dbName;
        }

        public override string[] GetTablePrimaryKeys(string tableName)
        {
            var lst = new List<string>();
            string sql = string.Format("SELECT k.column_name FROM information_schema.table_constraints t JOIN   information_schema.key_column_usage k USING     (constraint_name,table_schema,table_name) WHERE     t.constraint_type='PRIMARY KEY'AND  t.table_schema='{1}' AND t.table_name='{0}' ", tableName, GetDBName());
            using (IDataReader reader = QueryReader(sql, CommandType.Text, null))
            {
                while (reader.Read())
                {
                    lst.Add(reader[0].ToString());
                }
                reader.Close();
            }
            return lst.ToArray();
        }

        public override Dictionary<string, string> GetTables()
        {
            var dt = QueryTable("SELECT TABLE_NAME Name,TABLE_COMMENT Comment FROM information_schema.tables where TABLE_SCHEMA ='" + GetDBName() + "' ORDER BY TABLE_NAME; ");
            var result = new Dictionary<string, string>();
            foreach (DataRow row in dt.Rows)
            {
                string comment = null;
                if (row["Comment"] != DBNull.Value) comment = row["Comment"].ToString();
                result.Add((string)row["Name"], comment);
            }
            return result;
        }

        public override ForeignKeyItem[] GetTableForeignKeys(string tableName)
        {
            var dt = QueryTable(string.Format(@"select k.REFERENCED_TABLE_NAME FKTable,k.COLUMN_NAME Field,k.REFERENCED_COLUMN_NAME FKKeyField
 FROM information_schema.table_constraints t JOIN   information_schema.key_column_usage k USING     (constraint_name,table_schema,table_name)
WHERE  t.TABLE_SCHEMA='{0}'  and t.CONSTRAINT_TYPE ='FOREIGN KEY' and t.TABLE_NAME='{1}'", GetDBName(), tableName));
            ForeignKeyItem[] result = new ForeignKeyItem[dt.Rows.Count];
            for (var i = 0; i < result.Length; i++)
            {
                var row = dt.Rows[i];
                result[i] = new ForeignKeyItem { ColumnName = row["Field"].ToString(), ForeignTableName = row["FKTable"].ToString(), ForeignColumnName = row["FKKeyField"].ToString() };
            }
            return result;
        }
    }
}